package org.jeecg.modules.demo.task;

import io.swagger.annotations.ApiModel;
import lombok.*;
import lombok.experimental.Accessors;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.*;
import java.net.HttpURLConnection;
import java.net.URL;
import java.util.List;

public class ExcelReportGenerator {

    // 数据实体
    @Data
    @Accessors(chain = true)
    @EqualsAndHashCode(callSuper = false)
    @ApiModel(value="提交审核", description="提交审核")
    @Builder
    @NoArgsConstructor // 自动生成无参构造方法
    @AllArgsConstructor // 自动生成全参构造方法
    static class Item {
        private int index;
        private String name;
        private double quantity;
        private String unit;
        private double materialCost;
        private double auxCost;
        private double laborCost;
        private double totalCost;
        private String remark;
    }

    // 下载远程Excel模板
    public static File downloadTemplate(String fileUrl, String savePath) throws IOException {
        URL url = new URL(fileUrl);
        HttpURLConnection conn = (HttpURLConnection) url.openConnection();
        conn.setConnectTimeout(10000);
        conn.setReadTimeout(10000);
        conn.connect();

        try (InputStream in = conn.getInputStream();
             FileOutputStream out = new FileOutputStream(savePath)) {
            byte[] buffer = new byte[4096];
            int len;
            while ((len = in.read(buffer)) != -1) {
                out.write(buffer, 0, len);
            }
        }
        return new File(savePath);
    }

    // 生成报价单
    public static void generateExcel(File templateFile, String outputPath, String projectName,
                                     String projectNo, List<Item> items) throws Exception {
        try (FileInputStream fis = new FileInputStream(templateFile);
             Workbook workbook = new XSSFWorkbook(fis)) {

            Sheet sheet = workbook.getSheetAt(0);


            // === 固定位置写入 ===
            // 例如 名称在第4行第3列，项目编号在第4行第8列 （具体根据模板调整）
            Row row0 = sheet.getRow(3);
            row0.getCell(2).setCellValue(projectName);
            row0.getCell(7).setCellValue(projectNo);

            // === 明细写入 ===
            int startRow = 5; // 明细开始行（模板里从第6行开始，2基准）
            int rowIndex = startRow;
            for (Item item : items) {
                Row row = sheet.createRow(rowIndex++);
                row.createCell(1).setCellValue(item.getIndex());
                row.createCell(2).setCellValue(item.getName());
                row.createCell(3).setCellValue(item.getQuantity());
                row.createCell(4).setCellValue(item.getUnit());
                row.createCell(5).setCellValue(item.getMaterialCost());
                row.createCell(6).setCellValue(item.getAuxCost());
                row.createCell(7).setCellValue(item.getLaborCost());
                row.createCell(8).setCellValue(item.getTotalCost());
                row.createCell(9).setCellValue(item.getRemark());
            }

            // === 小计 ===
            Row subtotalRow = sheet.createRow(rowIndex++);
            subtotalRow.createCell(2).setCellValue("小计(不含税)");//3列
            double sum = items.stream().mapToDouble(Item::getTotalCost).sum();
            subtotalRow.createCell(8).setCellValue(sum);//第9列

            //// 合并 小计 文字单元格
            CellRangeAddress subtotalRegion = new CellRangeAddress(
                    subtotalRow.getRowNum(), // 起始行
                    subtotalRow.getRowNum(), // 结束行（同一行）
                    3,                       // 起始列
                    7                        // 结束列
            );
            sheet.addMergedRegion(subtotalRegion);


            // === 备注 ===
            Row remarkRow = sheet.createRow(rowIndex++);
            remarkRow.createCell(1).setCellValue("备注:");
            remarkRow.createCell(2).setCellValue("报价不含税, 含税另加9%");
            //// 合并 小计 文字单元格
            CellRangeAddress subtotalRegion1 = new CellRangeAddress(
                    remarkRow.getRowNum(), // 起始行
                    remarkRow.getRowNum(), // 结束行（同一行）
                    2,                       // 起始列
                    9                        // 结束列
            );
            sheet.addMergedRegion(subtotalRegion1);



            Row partyARow2 = sheet.createRow(rowIndex);
            //// 合并 小计 文字单元格
            CellRangeAddress subtotalRegion2 = new CellRangeAddress(
                    partyARow2.getRowNum(), // 起始行
                    partyARow2.getRowNum(), // 结束行（同一行）
                    1,                       // 起始列
                    9                        // 结束列
            );
            sheet.addMergedRegion(subtotalRegion2);

            // === 签字栏 ===
            rowIndex += 1;
            Row partyARow = sheet.createRow(rowIndex++);
            partyARow.createCell(1).setCellValue("甲方:");
            partyARow.createCell(2).setCellValue("华瑭");

            //// 合并 小计 文字单元格
            CellRangeAddress subtotalRegion3 = new CellRangeAddress(
                    partyARow.getRowNum(), // 起始行
                    partyARow.getRowNum(), // 结束行（同一行）
                    2,                       // 起始列
                    5                        // 结束列
            );
            sheet.addMergedRegion(subtotalRegion3);


            partyARow.createCell(6).setCellValue("乙方:");
            partyARow.createCell(7).setCellValue("上海南裕建筑装饰工程有限公司");
            //// 合并 小计 文字单元格
            CellRangeAddress subtotalRegion4 = new CellRangeAddress(
                    partyARow.getRowNum(), // 起始行
                    partyARow.getRowNum(), // 结束行（同一行）
                    7,                       // 起始列
                    9                        // 结束列
            );
            sheet.addMergedRegion(subtotalRegion4);



            Row partyBRow = sheet.createRow(rowIndex++);
            partyBRow.createCell(1).setCellValue("日期:");
            partyBRow.createCell(2).setCellValue("2028-09-01");
            //// 合并 小计 文字单元格
            CellRangeAddress subtotalRegion5 = new CellRangeAddress(
                    partyBRow.getRowNum(), // 起始行
                    partyBRow.getRowNum(), // 结束行（同一行）
                    2,                       // 起始列
                    5                        // 结束列
            );
            sheet.addMergedRegion(subtotalRegion5);


            partyBRow.createCell(6).setCellValue("日期:");
            partyBRow.createCell(7).setCellValue("2028-01-05");
            //// 合并 小计 文字单元格
            CellRangeAddress subtotalRegion6 = new CellRangeAddress(
                    partyBRow.getRowNum(), // 起始行
                    partyBRow.getRowNum(), // 结束行（同一行）
                    7,                       // 起始列
                    9                        // 结束列
            );
            sheet.addMergedRegion(subtotalRegion6);

            // === 整个区域加田字格边框 ===
            int startCellRow = 3;        // 明细开始行
            int endCellRow = rowIndex-1; // 明细 + 小计 + 备注 + 签字栏的最后一行
            int startCol = 1;
            int endCol = 9;

// 定义一个带四边边框的样式
            CellStyle borderStyle = workbook.createCellStyle();
            borderStyle.setBorderTop(BorderStyle.THIN);
            borderStyle.setBorderBottom(BorderStyle.THIN);
            borderStyle.setBorderLeft(BorderStyle.THIN);
            borderStyle.setBorderRight(BorderStyle.THIN);
            borderStyle.setVerticalAlignment(VerticalAlignment.CENTER);
            borderStyle.setAlignment(HorizontalAlignment.CENTER);

// 遍历区域内的每个单元格，应用样式
            for (int r = startCellRow; r <= endCellRow; r++) {
                Row row = sheet.getRow(r);
                if (row == null) row = sheet.createRow(r);
                for (int c = startCol; c <= endCol; c++) {
                    Cell cell = row.getCell(c);
                    if (cell == null) cell = row.createCell(c);
                    cell.setCellStyle(borderStyle);
                }
            }


            // === 输出 ===
            try (FileOutputStream fos = new FileOutputStream(outputPath)) {
                workbook.write(fos);
            }
        }
    }

    // 测试
    public static void main(String[] args) throws Exception {
        // 1. 下载模板
        File template = downloadTemplate("http://154.201.66.67:9000/blithe-crm/model/%E5%B7%A5%E7%A8%8B%E6%8A%A5%E4%BB%B7%E5%8D%95-%E6%A8%A1%E7%89%88.xlsx", "template.xlsx");

        // 2. 模拟数据
        List<Item> items = java.util.Arrays.asList(
                createItem(1, "圆形纤维织物风管", 25, "M", 1495, 30, 80, 40125, "300mm"),
                createItem(2, "圆形纤维织物风管", 76, "M", 560, 20, 40, 47120, "700mm")
        );

        // 3. 生成新文件
        generateExcel(template, "/Users/blithe/dev_tools/work/crm/jeecg-boot/jeecg-module-demo/output.xlsx", "BLP布袋风管改造", "项目001", items);
    }

    private static Item createItem(int index, String name, double qty, String unit,
                                   double material, double aux, double labor,
                                   double total, String remark) {
        Item item = new Item();
        item.setIndex(index);
        item.setName(name);
        item.setQuantity(qty);
        item.setUnit(unit);
        item.setMaterialCost(material);
        item.setAuxCost(aux);
        item.setLaborCost(labor);
        item.setTotalCost(total);
        item.setRemark(remark);
        return item;
    }
}
